package edu.psu.collegerecommendationtool.model;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;

import javax.faces.bean.*;

import edu.psu.collegerecommendationtool.commonutils.MySQLInfo;

@ManagedBean (name="userBean")
@SessionScoped
public class UserBean implements Serializable {
   private long user_id;
   private AccountInfo accountInfo;
   private DemographicInfo demographicInfo;
   private EducationInfo educationInfo;
   private PreferenceInfo preferenceInfo;
   private CollegeResult resultList;

   public UserBean () {
      this.user_id = 0;
      this.accountInfo = null;
      this.demographicInfo = null;
      this.educationInfo = null;
      this.preferenceInfo = null;
      this.preferenceInfo = null;
      this.resultList = null;
   }

   public UserBean (AccountInfo accountInfo, DemographicInfo demographicInfo,
                    EducationInfo educationInfo, PreferenceInfo preferenceInfo,
                    CollegeResult resultList)
   {
      this.user_id = 0;
      this.accountInfo = accountInfo;
      this.demographicInfo = demographicInfo;
      this.educationInfo = educationInfo;
      this.preferenceInfo = preferenceInfo;
      this.resultList = resultList;
   }

   public long getUser_id() {
      return user_id;
   }
   public void setUser_id(long user_id) {
      this.user_id = user_id;
   }

   public AccountInfo getAccountInfo() {
      return accountInfo;
   }
   public void setAccountInfo(AccountInfo newAccountInfo) {
      this.accountInfo = newAccountInfo;
   }

   public DemographicInfo getDemographicInfo() {
      return demographicInfo;
   }
   public void setDemographicInfo(DemographicInfo newDemographicInfo) {
      this.demographicInfo = newDemographicInfo;
   }

   public EducationInfo getEducationInfo() {
      return educationInfo;
   }
   public void setEducationInfo(EducationInfo newEducationInfo) {
      this.educationInfo = newEducationInfo;
   }

   public PreferenceInfo getPreferenceInfo() {
      return preferenceInfo;
   }
   public void setPreferenceInfo(PreferenceInfo newPreferenceInfo) {
      this.preferenceInfo = newPreferenceInfo;
   }

   public CollegeResult getResultList() {
      return resultList;
   }
   public void setResultList(CollegeResult resultList) {
      this.resultList = resultList;
   }

////////////////////////////////////////////////////////////////////////

   public String saveAccountInfo() {
      MySQLInfo mysqlinfo = new MySQLInfo();
      String DB_URL = mysqlinfo.getDBUrl();

//      String update_username = accountInfo.getUsername();
//      String update_password = accountInfo.getPassword();
//      String update_firstname = accountInfo.getFirstname();
//      String update_lastname = accountInfo.getLastname();
//      String update_email = accountInfo.getEmail();

      String sql = "UPDATE " + mysqlinfo.getDbName() + "." + mysqlinfo.getAcctable() +
    		       " SET username='" + accountInfo.getUsername() + 
                   "', password='" + accountInfo.getPassword() +
                   "', firstname='" + accountInfo.getFirstname() +
                   "', lastname='" + accountInfo.getLastname() +
                   "', email='" + accountInfo.getEmail() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return("MainPage");
   }

////////////////////////////////////////////////////////////////////////

   public String saveDemographicInfo() {
      MySQLInfo mysqlinfo = new MySQLInfo();
      String DB_URL = mysqlinfo.getDBUrl();

//      String update_state = demographicInfo.getState();
//      byte update_age = demographicInfo.getAge();
//      String update_sex = demographicInfo.getSex();
//      String update_race = demographicInfo.getRace();
//      String update_relative1 = demographicInfo.getRelative1();
//      String update_college1 = demographicInfo.getCollege1();
//      String update_relative2 = demographicInfo.getRelative2();
//      String update_college2 = demographicInfo.getCollege2();
//      String update_relative3 = demographicInfo.getRelative3();
//      String update_college3 = demographicInfo.getCollege3();
//      String update_relative4 = demographicInfo.getRelative4();
//      String update_college4 = demographicInfo.getCollege4();
//      String update_relative5 = demographicInfo.getRelative5();
//      String update_college5 = demographicInfo.getCollege5();

      String sql = "UPDATE " + mysqlinfo.getDbName() + "." + mysqlinfo.getDemtable() +
    		       " SET state='" + demographicInfo.getState() + 
    		       "', age='" + demographicInfo.getAge() +
    		       "', sex='" + demographicInfo.getSex() +
                   "', race='" + demographicInfo.getRace() +
                   "', relative1='" + demographicInfo.getRelative1() +
                   "', college1='" + demographicInfo.getCollege1() +
                   "', relative2='" + demographicInfo.getRelative2() +
                   "', college2='" + demographicInfo.getCollege2() +
                   "', relative3='" + demographicInfo.getRelative3() +
                   "', college3='" + demographicInfo.getCollege3() +
                   "', relative4='" + demographicInfo.getRelative4() +
                   "', college4='" + demographicInfo.getCollege4() +
                   "', relative5='" + demographicInfo.getRelative5() +
                   "', college5='" + demographicInfo.getCollege5() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return("MainPage");
   }

////////////////////////////////////////////////////////////////////////

   public String saveEducationInfo() {
      MySQLInfo mysqlinfo = new MySQLInfo();
      String DB_URL = mysqlinfo.getDBUrl();

//      float entered_GPA = educationInfo.getGPA();
//      short entered_mathSAT = educationInfo.getMathSAT();
//      short entered_creadSAT = educationInfo.getCreadSAT();
//      short entered_writeSAT = educationInfo.getWriteSAT();
//      byte entered_mathACT = educationInfo.getMathACT();
//      byte entered_readACT = educationInfo.getReadACT();
//      byte entered_englishACT = educationInfo.getEnglishACT();
//      byte entered_scienceACT = educationInfo.getScienceACT();

      String sql = "UPDATE " + mysqlinfo.getDbName() + "." + mysqlinfo.getEdutable() +
                   " SET GPA='" + educationInfo.getGPA() + 
                   "', mathSAT='" + educationInfo.getMathSAT() +
                   "', creadSAT='" + educationInfo.getCreadSAT() +
                   "', writeSAT='" + educationInfo.getWriteSAT() +
                   "', mathACT='" + educationInfo.getMathACT() +
                   "', readACT='" + educationInfo.getReadACT() +
                   "', englishACT='" + educationInfo.getEnglishACT() +
                   "', scienceACT='" + educationInfo.getScienceACT() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return("MainPage");
   }

////////////////////////////////////////////////////////////////////////

   public String savePreferenceInfo() {
      MySQLInfo mysqlinfo = new MySQLInfo();
      String DB_URL = mysqlinfo.getDBUrl();

//      String entered_location = preferenceInfo.getLocation();
//      String entered_pubpri = educationInfo.getPubpri();
//      String entered_size = educationInfo.getSize();
//      String entered_sexratio = educationInfo.getSexratio();

      String sql = "UPDATE " + mysqlinfo.getDbName() + "." + mysqlinfo.getPretable() +
                   " SET location='" + preferenceInfo.getLocation() + 
                   "', pubpri='" + preferenceInfo.getPubpri() +
                   "', size='" + preferenceInfo.getSize() +
                   "', sexratio='" + preferenceInfo.getSexratio() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return("MainPage");
   }

////////////////////////////////////////////////////////////////////////

   public String saveResultList() {
      MySQLInfo mysqlinfo = new MySQLInfo();
      String DB_URL = mysqlinfo.getDBUrl();

//      byte update_active = resultList.getActive();
//      String update_college1 = resultList.getCollege1();
//      String update_result1 = resultList.getResult1();
//      String update_college2 = resultList.getCollege2();
//      String update_result2 = resultList.getResult2();
//      String update_college3 = resultList.getCollege3();
//      String update_result3 = resultList.getResult3();
//      String update_college4 = resultList.getCollege4();
//      String update_result4 = resultList.getResult4();
//      String update_college5 = resultList.getCollege5();
//      String update_result5 = resultList.getResult5();

      String sql = "UPDATE " + mysqlinfo.getDbName() + "." + mysqlinfo.getRestable() +
                   " SET active='" + resultList.getActive() + 
                   "', college1='" + resultList.getCollege1() +
                   "', result1='" + resultList.getResult1() +
                   "', college2='" + resultList.getCollege2() +
                   "', result2='" + resultList.getResult2() +
                   "', college3='" + resultList.getCollege3() +
                   "', result3='" + resultList.getResult3() +
                   "', college4='" + resultList.getCollege4() +
                   "', result4='" + resultList.getResult4() +
                   "', college5='" + resultList.getCollege5() +
                   "', result5='" + resultList.getResult5() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;
      Connection con = getConnection(DB_URL);

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return("MainPage");
   }

////////////////////////////////////////////////////////////////////////

   public Connection getConnection(String DB_URL) {
      Connection con = null;

      try {
         con = DriverManager.getConnection(DB_URL);
      } catch (SQLException ex) {
         System.out.println(ex.getMessage());
      }
      finally { }

      return con;
   }


}